create or replace PACKAGE PK_EDI_MST IS

  -- Author  : HOP.DANG
  -- Created : 2013-01-24 6:13:57 PM
  -- Purpose : 
  ----------------------------------------------------------------
  --       PROCEDURES UPDATE SN of RDC
  ----------------------------------------------------------------
  PROCEDURE PR_XXVN_SN_RDC_IF;
  PROCEDURE PR_XVH_SN_CDC_IF;
  PROCEDURE PR_XXVN_UPDATE_ORDER_GTMS_IF;
  ----------------------------------------------------------------
  PROCEDURE PR_UPDATE_SN_RDC_HIST_IF
           (p_inv_org                VARCHAR2
           ,p_edi_no                 VARCHAR2
           ,p_serial_no              VARCHAR2
           ,p_box_no                 VARCHAR2
           ,p_ship_no                VARCHAR2
           ,p_line_no                VARCHAR2
           ,p_model_suffix           VARCHAR2
           ,p_out_date               VARCHAR2);      
  ----------------------------------------------------------------  
  PROCEDURE PR_UPDATE_SN_CDC_HIST_IF
           (p_inv_org                VARCHAR2
           ,p_model_suffix           VARCHAR2
           ,p_serial_no              VARCHAR2
           ,p_shipto_code            VARCHAR2
           ,p_shipto_date            VARCHAR2
           ,p_order_no               VARCHAR2
           ,p_load_plan_no           VARCHAR2);
  ----------------------------------------------------------------
  PROCEDURE PR_UPDATE_SN_RDC_HIST_IF01
           (p_inv_org                VARCHAR2
           ,p_edi_no                 VARCHAR2
           ,p_serial_no              VARCHAR2
           ,p_box_no                 VARCHAR2
           ,p_ship_no                VARCHAR2
           ,p_line_no                VARCHAR2
           ,p_model_suffix           VARCHAR2
           ,p_out_date               VARCHAR2);                                              
  ----------------------------------------------------------------
  --       PROCEDURES UPDATE DELIVERYs of RDC
  ----------------------------------------------------------------
  PROCEDURE PR_UPDATE_SO_IMEI_GSM 
           (p_imei_no                  VARCHAR2
           ,p_model                    VARCHAR2
           ,p_suffix                   VARCHAR2
           ,p_so_date                  VARCHAR2
           ,p_so_time                  VARCHAR2
           ,p_si_date                  VARCHAR2);
  ----------------------------------------------------------------
  --       PROCEDURES UPDATE IMEI of RDC
  ----------------------------------------------------------------         
  PROCEDURE PR_XXVN_DEL_RDC_IF;
  ----------------------------------------------------------------
  PROCEDURE PR_UPDATE_DEL_RDC_HIST_IF
           (p_inv_org                VARCHAR2
           ,p_delivery_no            VARCHAR2
           ,p_plan_no                VARCHAR2
           ,p_pick_no                VARCHAR2
           ,p_pick_date              VARCHAR2
           ,p_shipto_code            VARCHAR2
           ,p_shipto_name            VARCHAR2
           ,p_ship_date              VARCHAR2
           ,p_model_suffix           VARCHAR2
           ,p_delivery_address       VARCHAR2
           ,p_delivery_qty           VARCHAR2
           ,p_delivery_date          VARCHAR2
           ,p_delivery_status        VARCHAR2
           ,p_line_id                VARCHAR2);
  ----------------------------------------------------------------
  PROCEDURE PR_XXVN_SED_SHIPORDER_IF;
  ----------------------------------------------------------------
  PROCEDURE PR_UPDATE_ORDER_SHIP_HIST 
           (p_inv_org                VARCHAR2 
           ,p_city                   VARCHAR2
           ,p_shipto_code            VARCHAR2
           ,p_shipto_name            VARCHAR2
           ,p_order_no               VARCHAR2
           ,p_line_no                VARCHAR2
           ,p_line_status            VARCHAR2
           ,p_prod_div               VARCHAR2
           ,p_model_suffix           VARCHAR2
           ,p_order_qty              VARCHAR2
           ,p_release_qty            VARCHAR2
           ,p_unit_sell_price        VARCHAR2
           ,p_order_amt              VARCHAR2
           ,p_shipping_method        VARCHAR2
           ,p_request_shipped_date   VARCHAR2
           ,p_request_arrival_date   VARCHAR2
           ,p_shipping_remark        VARCHAR2
           ,p_invoice_no             VARCHAR2
           ,p_invoice_date           VARCHAR2
           ,p_booked_date            VARCHAR2
           ,p_pick_no                VARCHAR2
           ,p_pick_seq               VARCHAR2
           ,p_pick_release_date      VARCHAR2
           ,p_delivery_no            VARCHAR2
           ,p_subinventory           VARCHAR2
           ,p_creation_date          VARCHAR2
           ,p_billto_code            VARCHAR2
           ,p_billto_name            VARCHAR2
           ,p_saleschannel_code      VARCHAR2
           ,p_saleschannel_name      VARCHAR2
           ,p_created_by             VARCHAR2
           ,p_salesperson            VARCHAR2
           ,p_ar_flag                VARCHAR2   DEFAULT null
           ,p_au_code                VARCHAR2   DEFAULT null
           ,p_acceptance_code        VARCHAR2   DEFAULT null
           ,p_carrier                VARCHAR2   DEFAULT null  
           ,p_order_type_name        VARCHAR2   DEFAULT null
           ,p_cancel_flag            VARCHAR2   DEFAULT null);
  ----------------------------------------------------------------
           PROCEDURE PR_XXVN_MODEL_IF;
  ----------------------------------------------------------------
  PROCEDURE PR_UPDATE_MODEL_CAT      
           (p_prod_l1               VARCHAR2
           ,p_model                 VARCHAR2
           ,p_create_date           VARCHAR2
           ,p_suffix                VARCHAR2
           ,p_mp_model              VARCHAR2
           ,p_mp_suffix             VARCHAR2
           ,p_active_code           VARCHAR2
           ,p_sales_flag            VARCHAR2
           ,p_prod_l2               VARCHAR2
           ,p_prod_l3               VARCHAR2
           ,p_prod_l4               VARCHAR2
           ,p_tentative_flag        VARCHAR2
           ,p_model_spec            VARCHAR2
           ,p_pur_type              VARCHAR2
           ,p_enable_flag           VARCHAR2
           ,p_last_update_date      VARCHAR2
           ,p_unit                  VARCHAR2
           ,p_au_code               VARCHAR2
           ,p_af_code               VARCHAR2
           ,p_eom                   VARCHAR2
           ,p_prod_type             VARCHAR2
           ,p_mkt                   VARCHAR2 );
  ----------------------------------------------------------------
  PROCEDURE PR_UPDATE_CUST_EDI
           (p_custcode              VARCHAR2
           ,p_custkey               VARCHAR2
           ,p_serial_no             VARCHAR2
           ,p_model                 VARCHAR2
           ,p_sell_date             VARCHAR2
           ,p_eucell                VARCHAR2
           ,p_euname                VARCHAR2
           ,p_euadd                 VARCHAR2
           ,p_resptime              VARCHAR2
           ,p_edi_file              VARCHAR2);         
  ----------------------------------------------------------------
  FUNCTION FN_EDI_BOX_CHK
          (p_box_no                 VARCHAR2)
  RETURN                            VARCHAR2; 
  ----------------------------------------------------------------
  FUNCTION FN_EDI_RLT
          (p_filename                VARCHAR2
          ,p_filesize                VARCHAR2
          ,p_item                    VARCHAR2)
  RETURN                             VARCHAR2;
  ----------------------------------------------------------------
  PROCEDURE PR_SO_WT_MST_SW_EDI
           (out_cur            OUT   SYS_REFCURSOR); 
  ----------------------------------------------------------------
  PROCEDURE PR_SN_RDC_SW_EDI
           (out_cur            OUT   SYS_REFCURSOR); 
  ----------------------------------------------------------------
  PROCEDURE PR_SN_CDC_SW_EDI
           (out_cur            OUT   SYS_REFCURSOR); 
  ----------------------------------------------------------------
  PROCEDURE PR_MT_ADD_SEND;                                                                                                           
END PK_EDI_MST;
/

create or replace PACKAGE BODY PK_EDI_MST IS
  -----------------------------------------------------------------------------------------------------------------
  --                                PROCEDURE UPDATE SERIAL# FROM CDC W/Hs
  -----------------------------------------------------------------------------------------------------------------
  /*PROCEDURE PR_UPDATE_SN_CDC_HIST_IF
           (p_inv_org                     VARCHAR2
           ,p_loadplan_no                 VARCHAR2
           ,p_model_suffix                VARCHAR2
           ,p_serial_no                   VARCHAR2     
           ,p_box_no                      VARCHAR2
           ,p_shipto_code                 VARCHAR2
           ,p_shipto_name                 VARCHAR2
           ,p_shipto_date                 VARCHAR2
           ,p_order_no                    VARCHAR2
           ,p_closed_date                 VARCHAR2
           ,p_invoice_no                  VARCHAR2
           ,p_invoice_date                VARCHAR2
           ,p_customer_code               VARCHAR2
           ,p_customer_name               VARCHAR2)
  -----------------------------------------------------------------------------------------------------------------  
  IS
    v_model                               VARCHAR2(20) := SUBSTR(p_model_suffix,1,INSTR(p_model_suffix,'.')-1);
    v_suffix                              VARCHAR2(20) := SUBSTR(p_model_suffix,INSTR(p_model_suffix,'.')+1
                                                                 ,LENGTH(p_model_suffix) -INSTR(p_model_suffix,'.'));
        
    v_lot_sn                              VARCHAR2(50) := p_box_no;
   
  ------------------------------------------------------------------------------------------------------------------                                           
  BEGIN
    \*
    select a.boxid
    from   ezmes.lot@DBL_GMES a
    ,      ezmes.lotattr@DBL_GMES b
    where  a.lotid = b.lotid
    and    b.set_id = '409VCJZK0236';*\

    SELECT customer_model_serial_no
    INTO   v_lot_sn
    FROM   XXOWMM_MES_SN2_S_IF@DBL_MES
    WHERE  item_serial_no = p_box_no;
    \*
    SELECT DISTINCT(serial_no)
    INTO v_lot_sn
    FROM (SELECT DECODE(lot_set, null, lot_box, lot_set)  
                                          SERIAL_NO
          FROM   TB_WP_ADDWMSINPLOG@DBL_MES
          WHERE  actualtime = (SELECT max(actualtime)
                               FROM   TB_WP_ADDWMSINPLOG@DBL_MES
                               WHERE  lot_box = p_box_no)
          AND    lot_box = p_box_no
          AND    comm1                   != '*MANUAL*'
          GROUP BY lot_box, lot_set
         UNION
          SELECT DISTINCT(a.serial_no)    SERIAL_NO
          FROM LKALSMGR.TBL_SERIAL_NO_PRINTED_SERIAL@DBL_MES A,
               (SELECT work_order, serial_no 
                FROM LKALSMGR.TBL_SERIAL_NO_PRINTED_SERIAL@DBL_MES
                WHERE serial_no = p_box_no)                  B
          WHERE  a.work_order   = b.work_order
          AND    a.serial_no    = b.serial_no
          AND    a.serial_no_usage LIKE 'DISPLAY_SET%');*\
            
    INSERT INTO  TB_SN_CDC_HIST
                (inv_org
                ,loadplan_no
                ,model_suffix
                ,model
                ,suffix
                ,serial_no
                ,box_no
                ,shipto_code
                ,shipto_name
                ,shipto_date
                ,order_no
                ,closed_date
                ,invoice_no
                ,invoice_date
                ,customer_code
                ,customer_name)
    VALUES      
                (p_inv_org
                ,p_loadplan_no
                ,p_model_suffix
                ,v_model
                ,v_suffix
                ,v_lot_sn
                ,p_box_no
                ,p_shipto_code
                ,p_shipto_name
                ,TO_DATE(p_shipto_date,'YYYYMMDD')
                ,p_order_no
                ,TO_DATE(p_closed_date,'YYYYMMDD')
                ,p_invoice_no
                ,TO_DATE(p_invoice_date,'YYYYMMDD')
                ,p_customer_code
                ,p_customer_name);
            
    COMMIT;
            
    UPDATE       TB_EDI_RESULT
    SET          new_cnt      = TO_NUMBER(new_cnt) +1;
           
    COMMIT;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      BEGIN
        INSERT INTO  TB_SN_CDC_HIST
                    (inv_org
                    ,loadplan_no
                    ,model_suffix
                    ,model
                    ,suffix
                    ,serial_no
                    ,box_no
                    ,shipto_code
                    ,shipto_name
                    ,shipto_date
                    ,order_no
                    ,closed_date
                    ,invoice_no
                    ,invoice_date
                    ,customer_code
                    ,customer_name)
        VALUES
                    (p_inv_org
                    ,p_loadplan_no
                    ,p_model_suffix
                    ,v_model
                    ,v_suffix
                    ,p_serial_no
                    ,p_box_no
                    ,p_shipto_code
                    ,p_shipto_name
                    ,TO_DATE(p_shipto_date,'YYYYMMDD')
                    ,p_order_no
                    ,TO_DATE(p_closed_date,'YYYYMMDD')
                    ,p_invoice_no
                    ,TO_DATE(p_invoice_date,'YYYYMMDD')
                    ,p_customer_code
                    ,p_customer_name);
                  
        COMMIT;
        
        UPDATE       TB_EDI_RESULT
        SET          new_cnt       = TO_NUMBER(new_cnt) +1;
                  
        COMMIT;
      EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
          UPDATE     TB_EDI_RESULT
          SET        dup_cnt       = TO_NUMBER(dup_cnt) +1
                    ,dup_item      = dup_item || p_serial_no || ';';
          COMMIT; 
      END;
    WHEN DUP_VAL_ON_INDEX THEN NULL;
      UPDATE         TB_EDI_RESULT
      SET            dup_cnt       = TO_NUMBER(dup_cnt) +1
                    ,dup_item      = dup_item || p_serial_no || ';';
              
      COMMIT;
   WHEN TOO_MANY_ROWS THEN 
     BEGIN
       INSERT INTO  TB_SN_CDC_HIST
                   (inv_org
                   ,loadplan_no
                   ,model_suffix
                   ,model
                   ,suffix
                   ,serial_no
                   ,box_no
                   ,shipto_code
                   ,shipto_name
                   ,shipto_date
                   ,order_no
                   ,closed_date
                   ,invoice_no
                   ,invoice_date
                   ,customer_code
                   ,customer_name)
       VALUES
                   (p_inv_org
                   ,p_loadplan_no
                   ,p_model_suffix
                   ,v_model
                   ,v_suffix
                   ,p_serial_no
                   ,p_box_no
                   ,p_shipto_code
                   ,p_shipto_name
                   ,TO_DATE(p_shipto_date,'YYYYMMDD')
                   ,p_order_no
                   ,TO_DATE(p_closed_date,'YYYYMMDD')
                   ,p_invoice_no
                   ,TO_DATE(p_invoice_date,'YYYYMMDD')
                   ,p_customer_code
                   ,p_customer_name);
                  
       COMMIT;
     EXCEPTION
       WHEN DUP_VAL_ON_INDEX THEN
         UPDATE     tb_edi_result
         SET        dup_cnt  = TO_NUMBER(dup_cnt) +1
                   ,dup_item = dup_item || p_serial_no || ';';
         COMMIT;         
    END;              
  END PR_UPDATE_SN_CDC_HIST_IF;*/
  ----------------------------------------------------------------------------------------------------------------- 
  --                                PROCEDURE UPDATE SERIAL# FROM RDC W/Hs
  ----------------------------------------------------------------------------------------------------------------- 
  PROCEDURE PR_XXVN_SN_RDC_IF
  IS      --                                    PRAGMA AUTONOMOUS_TRANSACTION;
   -- cur     SYS_REFCURSOR;
   v_sqlerrm                              VARCHAR2(4000);                                 
  -----------------------------------------------------------------------------------------------------------------
  BEGIN
    BEGIN
      INSERT INTO VNXX_EDI_RESULT
                 (edi_date
                 ,edi_time
                 ,edi_type
                 ,new_cnt
                 ,dup_cnt
                 ,dup_item)
      SELECT      TO_CHAR(sysdate,'YYYYMMDD')
                 ,sysdate
                 ,'SN_RDC'
                 ,0
                 ,0
                 ,null
                  FROM dual;
    
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN NULL;  
    END;          
  -----------------------------------------------------------------------------------------------------------------  
    FOR cur IN(
      SELECT   rowid                          
              ,organization_code              inv_org
              ,pick_order_no                  edi_no
              ,serial_no                      serial_no
              ,source_header_no               order_no
              ,pick_seq_no                    line_no
              ,item_code                      model_suffix
              ,TO_CHAR(transfer_date,'YYYYMMDD')           
                                              out_date       
      FROM     VNXX_SHPSERIAL_IF@DBL_MES
      WHERE    transfer_flag                = 'N')
     -- AND      step_status_code             = 'SUCCESS')
    
    LOOP
      BEGIN
        PR_UPDATE_SN_RDC_HIST_IF(cur.inv_org
                                ,cur.edi_no
                                ,cur.serial_no
                                ,cur.serial_no
                                ,cur.order_no
                                ,cur.line_no
                                ,cur.model_suffix
                                ,cur.out_date);
        
        UPDATE VNXX_SHPSERIAL_IF@DBL_MES
        SET    transfer_flag                = 'Y'
              ,transfer_date                = sysdate 
        WHERE  rowid                        = cur.rowid;
        
        COMMIT;
      EXCEPTION
        WHEN OTHERS THEN
          v_sqlerrm                        := SQLERRM;                      
          update VNXX_SHPSERIAL_IF@DBL_MES
          set    edi_error_message_text     = v_sqlerrm
                ,transfer_date              = sysdate 
          where  rowid                      = cur.rowid;
                  
          NULL;
      END;                                             
    END LOOP;       
  END PR_XXVN_SN_RDC_IF;
  -----------------------------------------------------------------------------------------------------------------  
  -----------------------------------------------------------------------------------------------------------------
  PROCEDURE PR_XVH_SN_CDC_IF
  IS        
    v_sqlerrm                              VARCHAR2(4000);
  -----------------------------------------------------------------------------------------------------------------
  BEGIN
    BEGIN
      INSERT INTO VNXX_EDI_RESULT
                 (edi_date
                 ,edi_time
                 ,edi_type
                 ,new_cnt
                 ,dup_cnt
                 ,dup_item)
      SELECT      TO_CHAR(sysdate,'YYYYMMDD')
                 ,sysdate
                 ,'SN_CDC'
                 ,0
                 ,0
                 ,null
                  FROM dual;
    
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN NULL;  
    END;
    ---------------------------------------------------------------------------------------------------------------
    FOR cur IN (SELECT rowid               
                      ,organization_code inv_org
                      ,source_header_no  order_no
                      ,source_line_no    line_no
                      ,delivery_no       delivery_no
                      ,'N/A'             shipto_code
                      ,TO_CHAR(wms_create_date,'YYYYMMDD')
                                         shipto_date
                      ,item_code         model_suffix
                      ,serial_no         serial_no
                      ,'N/A'             load_plan_no
                FROM   XVH_SHPSERIAL_IF@VHDEV      
                WHERE  transfer_flag = 'N')
    LOOP
      BEGIN
        PR_UPDATE_SN_CDC_HIST_IF(cur.inv_org
                                ,cur.model_suffix
                                ,cur.serial_no   
                                ,cur.shipto_code
                                ,cur.shipto_date
                                ,cur.order_no
                                ,cur.load_plan_no);
                                
        UPDATE XVH_SHPSERIAL_IF@VHDEV
        SET    transfer_flag     = 'Y'
              ,transfer_date     = sysdate 
        WHERE  rowid             = cur.rowid;
        
        COMMIT;
      EXCEPTION
        WHEN OTHERS THEN
          v_sqlerrm             := SQLERRM;                      
          update XVH_SHPSERIAL_IF@VHDEV
          set    error_message_text
                                 = v_sqlerrm
                ,transfer_date   = sysdate 
          where  rowid           = cur.rowid;
          
          COMMIT;        
          NULL;                          
      END;  
    END LOOP;                                    
  END       PR_XVH_SN_CDC_IF;  
  -----------------------------------------------------------------------------------------------------------------
  --                                PROCEDURE UPDATE DELIVERY & SHIPPING FROM RDC W/Hs - GTMS
  -----------------------------------------------------------------------------------------------------------------
  PROCEDURE PR_XXVN_UPDATE_ORDER_GTMS_IF
  IS 
    v_sqlerrm                             VARCHAR2(4000);
    v_exist                               VARCHAR2(0002);
  BEGIN
    FOR cur IN(
      SELECT   rowid                     
              ,organization_code         inv_org
              ,source_header_no          order_no
              ,source_line_no            line_no
              ,delivery_no               delivery_no
              ,pick_order_no             pick_no
              ,pick_seq_no               pick_seq
              ,TO_CHAR(transaction_date,'YYYYMMDD')
                                         pick_date
              ,DECODE(ship_to_customer_code, NULL, 'N/A', ship_to_customer_code)     
                                         shipto_code
              ,DECODE(bill_to_customer_code, NULL, 'N/A', bill_to_customer_code)
                                         billto_code    
              ,subinventory_code         subinv_code
              ,carrier_code              carrier_code
              ,source_code               source_code
              ,TO_CHAR(transaction_date,'YYYYMMDD')
                                         ship_date
              ,SUBSTR(TRIM(item_code),1,INSTR(TRIM(item_code),'.')-1)                 
                                         model
              ,SUBSTR(TRIM(item_code),INSTR(TRIM(item_code),'.')+1 
                                                                ,LENGTH(TRIM(item_code)) -INSTR(TRIM(item_code),'.')) 
                                         suffix        
              ,TRIM(item_code)           model_suffix                                                                    
              ,delivery_qty              delivery_qty
              ,TO_CHAR(transfer_date,'YYYYMMDD')
                                         delivery_date
              ,DECODE(status_code, null, 'CLOS', status_code)
                                         delivery_status
              ,cancel_flag               cancel_flag                           
      FROM     VNXX_SHPCFM_IF@DBL_MES
      WHERE    transfer_flag           NOT IN ('Y'))
   --   AND      step_status_code        = 'SUCCESS')
      
    LOOP
      BEGIN
        SELECT distinct('OK')
        INTO    v_exist
        FROM    TB_ORDER_SHIP_HIST
        WHERE   inv_org                  = cur.inv_org 
        AND     order_no                 = cur.order_no
        AND     model                    = cur.model
        AND     suffix                   = cur.suffix
        AND     shipto_code              = cur.shipto_code;
    --    AND     release_qty              = cur.delivery_qty;
        
        UPDATE VNXX_SHPCFM_IF@DBL_MES
        SET    transfer_flag   = 'Y'
              ,transfer_date   = sysdate 
        WHERE  rowid           = cur.rowid;
        
        COMMIT;                                              
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          IF (cur.pick_no IS NOT NULL) AND (cur.suffix NOT IN ('KMAI')) THEN
             INSERT INTO TB_ORDER_SHIP_HIST(
                         inv_org
                        ,order_no
                        ,order_qty
                        ,line_no
                        ,release_qty
                        ,pick_no
                        ,pick_seq
                        ,pick_release_date
                        ,model
                        ,suffix
                        ,delivery_no
                        ,shipto_code
                        ,billto_code
                        ,billto_name
                        ,subinventory
                        ,carrier
                        ,cancel_flag
                        ,request_arrival_date
                        ,model_suffix)
             VALUES    (cur.inv_org
                       ,cur.order_no
                       ,0
                       ,cur.line_no
                       ,cur.delivery_qty
                       ,cur.pick_no
                       ,cur.pick_seq 
                       ,TO_DATE(cur.pick_date,'YYYYMMDD')
                       ,cur.model
                       ,cur.suffix
                       ,cur.delivery_no
                       ,cur.shipto_code
                       ,cur.billto_code
                       ,(SELECT distinct(short_name)
                         FROM   tb_cm_billto_inf
                         WHERE  billto_code = cur.billto_code)       
                       ,cur.subinv_code
                       ,cur.carrier_code
                       ,cur.cancel_flag
                       ,TO_DATE(cur.pick_date,'YYYYMMDD')
                       ,cur.model_suffix);
            
            UPDATE VNXX_SHPCFM_IF@DBL_MES
            SET    transfer_flag   = 'Y'
                  ,transfer_date   = sysdate 
            WHERE  rowid           = cur.rowid;
            COMMIT;
          ELSIF (cur.suffix IN ('KMAI')) THEN
            UPDATE VNXX_SHPCFM_IF@DBL_MES
            SET    transfer_flag   = 'Y'
                  ,transfer_date   = sysdate 
            WHERE  rowid           = cur.rowid;
            COMMIT;                      
          END IF;
        WHEN OTHERS THEN
          v_sqlerrm                        := SQLERRM;                      
          update VNXX_SHPCFM_IF@DBL_MES
          set    edi_error_message_text     = v_sqlerrm
                ,transfer_date              = sysdate 
          where  rowid                      = cur.rowid;
          ---dbms_output.put_line ('PR_XXVN_DEL_RDC_IF :' || SQLERRM);
          
          NULL;
      END;  
    END LOOP;       
  END PR_XXVN_UPDATE_ORDER_GTMS_IF;
  -----------------------------------------------------------------------------------------------------------------
  PROCEDURE PR_UPDATE_SN_RDC_HIST_IF 
           (p_inv_org                  VARCHAR2
           ,p_edi_no                   VARCHAR2
           ,p_serial_no                VARCHAR2
           ,p_box_no                   VARCHAR2
           ,p_ship_no                  VARCHAR2
           ,p_line_no                  VARCHAR2
           ,p_model_suffix             VARCHAR2
           ,p_out_date                 VARCHAR2) 
  -----------------------------------------------------------------------------------------------------------------
  IS
    v_model                            VARCHAR2(20)  := SUBSTR(p_model_suffix,1,INSTR(p_model_suffix,'.')-1);
    v_suffix                           VARCHAR2(20)  := SUBSTR(p_model_suffix,INSTR(p_model_suffix,'.')+1 
                                                               ,LENGTH(p_model_suffix) -INSTR(p_model_suffix,'.'));
        
    v_sn_no                            VARCHAR2(50)  := p_serial_no;
    v_box_no                           VARCHAR2(50)  := p_box_no;
    v_sn_len                           NUMBER(2)     := LENGTH(v_sn_no);
    v_out_date                         DATE          := TO_DATE(p_out_date, 'YYYYMMDD');
    v_sn_l                             NUMBER(2)     := 0;
    v_sn_r                             NUMBER(2)     := 0;
    v_sn_type                          VARCHAR2(50)  := null;                                                       
  -----------------------------------------------------------------------------------------------------------------
  BEGIN
    --SERIAL_NO IS IN NORMAL CASES
    IF v_sn_len IN (9,10,11,12,13) THEN
      BEGIN
        INSERT INTO TB_SN_RDC_HIST
                   (inv_org
                   ,edi_no
                   ,serial_no
                   ,box_no
                   ,ship_no
                   ,line_no
                   ,model_suffix
                   ,model
                   ,suffix
                   ,out_date)
        VALUES      
                  (p_inv_org
                  ,p_edi_no
                  ,v_sn_no
                  ,p_box_no
                  ,p_ship_no
                  ,p_line_no
                  ,p_model_suffix
                  ,v_model
                  ,v_suffix
                  ,v_out_date);
                        
        UPDATE     VNXX_EDI_RESULT
        SET        new_cnt       = TO_NUMBER(new_cnt) +1
        WHERE      edi_date      = TO_CHAR(sysdate,'YYYYMMDD')
        AND        edi_type      = 'SN_RDC';
            
        COMMIT;            
      EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
          UPDATE    VNXX_EDI_RESULT
          SET       dup_cnt      = TO_NUMBER(dup_cnt) +1
                   ,dup_item     = dup_item || p_serial_no || ';'
          WHERE     edi_date     = TO_CHAR(sysdate,'YYYYMMDD')
          AND       edi_type     = 'SN_RDC';         
          
          COMMIT;
          
          NULL;   
      END;
    -----------------------------------------------------------------------------------------------------------------
    ELSE  
      BEGIN--0
        SELECT DISTINCT(lot_set)
        INTO   v_sn_no
        FROM  (SELECT     DISTINCT(DECODE(lot_set, null, lot_box, lot_set))            
                                                        LOT_SET
                         ,p_mes.lot_box
               FROM       TB_WP_ADDWMSINPLOG@DBL_MES    p_mes --//TB_SFC_OWM_LOGIS_SERIAL_NO_REC
               WHERE      COMM1                      != '*MANUAL*'
               GROUP BY   p_mes.lot_set
                         ,p_mes.lot_box
               UNION
               SELECT     cbu_mes.lot_set               LOT_SET
                         ,cbu_mes.lot_box 
               FROM       VNXX_SN_CBU_HIST_IF cbu_mes)  A
               WHERE      a.lot_box = v_box_no;
    -----------------------------------------------------------------------------------------------------------------
        BEGIN--1
          INSERT INTO TB_SN_RDC_HIST
                     (inv_org
                     ,edi_no
                     ,serial_no
                     ,box_no
                     ,ship_no
                     ,line_no
                     ,model_suffix
                     ,model
                     ,suffix
                     ,out_date)
          VALUES      
                     (p_inv_org
                     ,p_edi_no
                     ,v_sn_no
                     ,p_box_no
                     ,p_ship_no
                     ,p_line_no
                     ,p_model_suffix
                     ,v_model
                     ,v_suffix
                     ,v_out_date);
          COMMIT;
              
          UPDATE      VNXX_EDI_RESULT
          SET         new_cnt        = TO_NUMBER(new_cnt) +1
          WHERE       edi_date       = TO_CHAR(sysdate,'YYYYMMDD')
          AND         edi_type       = 'SN_RDC';
              
          COMMIT;            
        EXCEPTION
          WHEN DUP_VAL_ON_INDEX THEN
            UPDATE    VNXX_EDI_RESULT
            SET       dup_cnt  = TO_NUMBER(dup_cnt) +1
                     ,dup_item = dup_item || p_serial_no || ';'
            WHERE     edi_date = TO_CHAR(sysdate,'YYYYMMDD')
            AND       edi_type = 'SN_RDC';         
                             
            COMMIT;
        END;--#1            
    -----------------------------------------------------------------------------------------------------------------            
      EXCEPTION--0
        WHEN NO_DATA_FOUND THEN
          BEGIN--2
            SELECT DISTINCT(a.serial_no)
            INTO   v_sn_no 
            FROM lkalsmgr.tbl_serial_no_printed_serial@dbl_mes           A,
                (SELECT work_order, serial_no 
                 FROM   lkalsmgr.tbl_serial_no_printed_serial@dbl_mes
                 WHERE  serial_no    = v_box_no)                         B
            WHERE       a.work_order = b.work_order
            AND         a.serial_no  = b.serial_no
            AND         a.serial_no_usage LIKE 'DISPLAY_SET%'; 
    -----------------------------------------------------------------------------------------------------------------
            BEGIN--2.1
              INSERT INTO TB_SN_RDC_HIST
                         (inv_org
                         ,edi_no
                         ,serial_no
                         ,box_no
                         ,ship_no
                         ,line_no
                         ,model_suffix
                         ,model
                         ,suffix
                         ,out_date)
              VALUES      
                         (p_inv_org
                         ,p_edi_no
                         ,v_sn_no
                         ,p_box_no
                         ,p_ship_no
                         ,p_line_no
                         ,p_model_suffix
                         ,v_model
                         ,v_suffix
                         ,v_out_date);
              
              COMMIT;
                  
              UPDATE      VNXX_EDI_RESULT
              SET         new_cnt        = TO_NUMBER(new_cnt) +1
              WHERE       edi_date       = TO_CHAR(sysdate,'YYYYMMDD')
              AND         edi_type       = 'SN_RDC';
              
              COMMIT;            
            EXCEPTION--2.1
              WHEN DUP_VAL_ON_INDEX THEN
                UPDATE    VNXX_EDI_RESULT
                SET       dup_cnt        = TO_NUMBER(dup_cnt) +1
                         ,dup_item       = dup_item || p_serial_no || ';'
                WHERE     edi_date       = TO_CHAR(sysdate,'YYYYMMDD')
                AND       edi_type       = 'SN_RDC';         
                                 
                COMMIT;
            END;--#2.1
      -----------------------------------------------------------------------------------------------------------------  
          EXCEPTION--2
            WHEN NO_DATA_FOUND THEN
              BEGIN--3
                SELECT TO_NUMBER(sn_left,'99')
                      ,TO_NUMBER(sn_right,'99')
                      ,set_box
                INTO   v_sn_l
                      ,v_sn_r
                      ,v_sn_type
                FROM   TB_CM_SN_INF_DEC
                WHERE  TO_NUMBER(sn_len) = v_sn_len;

                v_sn_no := SUBSTR(v_sn_no, v_sn_l + 1, (v_sn_len-v_sn_l-v_sn_r));
        -----------------------------------------------------------------------------------------------------------------
                BEGIN--4
                  INSERT INTO TB_SN_RDC_HIST
                             (inv_org
                             ,edi_no
                             ,serial_no
                             ,box_no
                             ,ship_no
                             ,line_no
                             ,model_suffix
                             ,model
                             ,suffix
                             ,out_date)
                  VALUES      
                             (p_inv_org
                             ,p_edi_no
                             ,v_sn_no
                             ,p_box_no
                             ,p_ship_no
                             ,p_line_no
                             ,p_model_suffix
                             ,v_model
                             ,v_suffix
                             ,v_out_date);

                  UPDATE      VNXX_EDI_RESULT
                  SET         new_cnt        = TO_NUMBER(new_cnt) +1
                  WHERE       edi_date       = TO_CHAR(sysdate,'YYYYMMDD')
                  AND         edi_type       = 'SN_RDC';
                      
                  COMMIT;            
                EXCEPTION--4
                  WHEN DUP_VAL_ON_INDEX THEN
                    UPDATE    VNXX_EDI_RESULT
                    SET       dup_cnt        = TO_NUMBER(dup_cnt) +1
                             ,dup_item       = dup_item || p_serial_no || ';'
                    WHERE     edi_date       = TO_CHAR(sysdate,'YYYYMMDD')
                    AND       edi_type       = 'SN_RDC';         
                                       
                    COMMIT;
                END;--#4
         -----------------------------------------------------------------------------------------------------------------              
              EXCEPTION--3
                WHEN OTHERS THEN 
                  INSERT INTO TB_SN_RDC_HIST
                             (inv_org
                             ,edi_no
                             ,serial_no
                             ,box_no
                             ,ship_no
                             ,line_no
                             ,model_suffix
                             ,model
                             ,suffix
                             ,out_date)
                 VALUES      
                             (p_inv_org
                             ,p_edi_no
                             ,p_serial_no
                             ,p_box_no
                             ,p_ship_no
                             ,p_line_no
                             ,p_model_suffix
                             ,v_model
                             ,v_suffix
                             ,v_out_date);
                  
                  COMMIT;
                                
                  UPDATE       VNXX_EDI_RESULT
                  SET          new_cnt        = TO_NUMBER(new_cnt) +1
                  WHERE        edi_date       = TO_CHAR(sysdate,'YYYYMMDD')
                  AND          edi_type       = 'SN_RDC';
                  
                  COMMIT;
              END;--#3
            WHEN TOO_MANY_ROWS THEN NULL;
          END; --#2
        WHEN TOO_MANY_ROWS THEN NULL;
      END; --#1  
    END IF;
  END PR_UPDATE_SN_RDC_HIST_IF;
  -----------------------------------------------------------------------------------------------------------------  
  -----------------------------------------------------------------------------------------------------------------
  PROCEDURE PR_UPDATE_SN_RDC_HIST_IF01 
           (p_inv_org                  VARCHAR2
           ,p_edi_no                   VARCHAR2
           ,p_serial_no                VARCHAR2
           ,p_box_no                   VARCHAR2
           ,p_ship_no                  VARCHAR2
           ,p_line_no                  VARCHAR2
           ,p_model_suffix             VARCHAR2
           ,p_out_date                 VARCHAR2) 
  -----------------------------------------------------------------------------------------------------------------
  IS
    v_model                            VARCHAR2(20)  := SUBSTR(p_model_suffix,1,INSTR(p_model_suffix,'.')-1);
    v_suffix                           VARCHAR2(20)  := SUBSTR(p_model_suffix,INSTR(p_model_suffix,'.')+1 
                                                               ,LENGTH(p_model_suffix) -INSTR(p_model_suffix,'.'));
        
    v_sn_no                            VARCHAR2(50)  := p_serial_no;
    v_box_no                           VARCHAR2(50)  := p_box_no;
    v_sn_len                           NUMBER(2)     := LENGTH(v_sn_no);
    v_out_date                         DATE          := TO_DATE(p_out_date, 'YYYYMMDD');
    v_sn_l                             NUMBER(2)     := 0;
    v_sn_r                             NUMBER(2)     := 0;
    v_sn_type                          VARCHAR2(50)  := null;                                                       
  -----------------------------------------------------------------------------------------------------------------
  BEGIN
    --SERIAL_NO IS IN NORMAL CASES
    IF v_sn_len IN (9,10,11,12,13) THEN
      BEGIN
        INSERT INTO TB_SN_RDC_HIST
                   (inv_org
                   ,edi_no
                   ,serial_no
                   ,box_no
                   ,ship_no
                   ,line_no
                   ,model_suffix
                   ,model
                   ,suffix
                   ,out_date)
        VALUES      
                  (p_inv_org
                  ,p_edi_no
                  ,v_sn_no
                  ,p_box_no
                  ,p_ship_no
                  ,p_line_no
                  ,p_model_suffix
                  ,v_model
                  ,v_suffix
                  ,v_out_date);
                        
       /* UPDATE     TB_EDI_RESULT
        SET        new_cnt       = TO_NUMBER(new_cnt) +1;
        \*WHERE      edi_date      = TO_CHAR(sysdate,'YYYYMMDD')
        AND        edi_type      = 'SN';*\
            */
        COMMIT;            
      EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN NULL;
        /* \* UPDATE    TB_EDI_RESULT
          SET       dup_cnt      = TO_NUMBER(dup_cnt) +1
                   ,dup_item     = dup_item || p_serial_no || ';';
          \*WHERE     edi_date     = TO_CHAR(sysdate,'YYYYMMDD')
          AND       edi_type     = 'SN';     *\    
          
          COMMIT;
          
          NULL;   *\*\*/
      END;
    END IF;  
    -----------------------------------------------------------------------------------------------------------------
    /*ELSE  
      BEGIN--0
        SELECT DISTINCT(lot_set)
        INTO   v_sn_no
        FROM  (SELECT     DISTINCT(DECODE(lot_set, null, lot_box, lot_set))            
                                                        LOT_SET
                         ,p_mes.lot_box
               FROM       TB_WP_ADDWMSINPLOG@DBL_MES    p_mes
               WHERE      COMM1                      != '*MANUAL*' 
               GROUP BY   p_mes.lot_set
                         ,p_mes.lot_box
               UNION
               SELECT     cbu_mes.lot_set               LOT_SET
                         ,cbu_mes.lot_box 
               FROM       VNXX_SN_CBU_HIST_IF cbu_mes)  A
               WHERE      a.lot_box = v_box_no;
    -----------------------------------------------------------------------------------------------------------------
        BEGIN--1
          INSERT INTO TB_SN_RDC_HIST
                     (inv_org
                     ,edi_no
                     ,serial_no
                     ,box_no
                     ,ship_no
                     ,line_no
                     ,model_suffix
                     ,model
                     ,suffix
                     ,out_date)
          VALUES      
                     (p_inv_org
                     ,p_edi_no
                     ,v_sn_no
                     ,p_box_no
                     ,p_ship_no
                     ,p_line_no
                     ,p_model_suffix
                     ,v_model
                     ,v_suffix
                     ,v_out_date);
          COMMIT;
              
          UPDATE      TB_EDI_RESULT
          SET         new_cnt        = TO_NUMBER(new_cnt) +1;
         \* WHERE       edi_date       = TO_CHAR(sysdate,'YYYYMMDD')
          AND         edi_type       = 'SN';*\
              
          COMMIT;            
        EXCEPTION
          WHEN DUP_VAL_ON_INDEX THEN
            UPDATE    TB_EDI_RESULT
            SET       dup_cnt  = TO_NUMBER(dup_cnt) +1
                     ,dup_item = dup_item || p_serial_no || ';';
           \* WHERE     edi_date = TO_CHAR(sysdate,'YYYYMMDD')
            AND       edi_type = 'SN';     *\    
                             
            COMMIT;
        END;--#1            
    -----------------------------------------------------------------------------------------------------------------            
      EXCEPTION--0
        WHEN NO_DATA_FOUND THEN
          BEGIN--2
            SELECT DISTINCT(a.serial_no)
            INTO   v_sn_no 
            FROM lkalsmgr.tbl_serial_no_printed_serial@dbl_mes           A,
                (SELECT work_order, serial_no 
                 FROM   lkalsmgr.tbl_serial_no_printed_serial@dbl_mes
                 WHERE  serial_no    = v_box_no)                         B
            WHERE       a.work_order = b.work_order
            AND         a.serial_no  = b.serial_no
            AND         a.serial_no_usage LIKE 'DISPLAY_SET%'; 
    -----------------------------------------------------------------------------------------------------------------
            BEGIN--2.1
              INSERT INTO TB_SN_RDC_HIST
                         (inv_org
                         ,edi_no
                         ,serial_no
                         ,box_no
                         ,ship_no
                         ,line_no
                         ,model_suffix
                         ,model
                         ,suffix
                         ,out_date)
              VALUES      
                         (p_inv_org
                         ,p_edi_no
                         ,v_sn_no
                         ,p_box_no
                         ,p_ship_no
                         ,p_line_no
                         ,p_model_suffix
                         ,v_model
                         ,v_suffix
                         ,v_out_date);
              
              COMMIT;
                  
              UPDATE      TB_EDI_RESULT
              SET         new_cnt        = TO_NUMBER(new_cnt) +1;
             \* WHERE       edi_date       = TO_CHAR(sysdate,'YYYYMMDD')
              AND         edi_type       = 'SN';*\
              
              COMMIT;            
            EXCEPTION--2.1
              WHEN DUP_VAL_ON_INDEX THEN
                UPDATE    TB_EDI_RESULT
                SET       dup_cnt        = TO_NUMBER(dup_cnt) +1
                         ,dup_item       = dup_item || p_serial_no || ';';
               \* WHERE     edi_date       = TO_CHAR(sysdate,'YYYYMMDD')
                AND       edi_type       = 'SN';    *\     
                                 
                COMMIT;
            END;--#2.1
      -----------------------------------------------------------------------------------------------------------------  
          EXCEPTION--2
            WHEN NO_DATA_FOUND THEN
              BEGIN--3
                SELECT TO_NUMBER(sn_left,'99')
                      ,TO_NUMBER(sn_right,'99')
                      ,set_box
                INTO   v_sn_l
                      ,v_sn_r
                      ,v_sn_type
                FROM   TB_CM_SN_INF_DEC
                WHERE  TO_NUMBER(sn_len) = v_sn_len;

                v_sn_no := SUBSTR(v_sn_no, v_sn_l + 1, (v_sn_len-v_sn_l-v_sn_r));
        -----------------------------------------------------------------------------------------------------------------
                BEGIN--4
                  INSERT INTO TB_SN_RDC_HIST
                             (inv_org
                             ,edi_no
                             ,serial_no
                             ,box_no
                             ,ship_no
                             ,line_no
                             ,model_suffix
                             ,model
                             ,suffix
                             ,out_date)
                  VALUES      
                             (p_inv_org
                             ,p_edi_no
                             ,v_sn_no
                             ,p_box_no
                             ,p_ship_no
                             ,p_line_no
                             ,p_model_suffix
                             ,v_model
                             ,v_suffix
                             ,v_out_date);

                  UPDATE      TB_EDI_RESULT
                  SET         new_cnt        = TO_NUMBER(new_cnt) +1;
                  \*WHERE       edi_date       = TO_CHAR(sysdate,'YYYYMMDD')
                  AND         edi_type       = 'SN';*\
                      
                  COMMIT;            
                EXCEPTION--4
                  WHEN DUP_VAL_ON_INDEX THEN
                    UPDATE    TB_EDI_RESULT
                    SET       dup_cnt        = TO_NUMBER(dup_cnt) +1
                             ,dup_item       = dup_item || p_serial_no || ';';
                    \*WHERE     edi_date       = TO_CHAR(sysdate,'YYYYMMDD')
                    AND       edi_type       = 'SN';         *\
                                       
                    COMMIT;
                END;--#4
         -----------------------------------------------------------------------------------------------------------------              
              EXCEPTION--3
                WHEN OTHERS THEN
                  BEGIN  
                    INSERT INTO TB_SN_RDC_HIST
                               (inv_org
                               ,edi_no
                               ,serial_no
                               ,box_no
                               ,ship_no
                               ,line_no
                               ,model_suffix
                               ,model
                               ,suffix
                               ,out_date)
                   VALUES      
                               (p_inv_org
                               ,p_edi_no
                               ,p_serial_no
                               ,p_box_no
                               ,p_ship_no
                               ,p_line_no
                               ,p_model_suffix
                               ,v_model
                               ,v_suffix
                               ,v_out_date);
                    
                    COMMIT;
                                  
                    UPDATE       TB_EDI_RESULT
                    SET          new_cnt        = TO_NUMBER(new_cnt) +1;
                    \*WHERE        edi_date       = TO_CHAR(sysdate,'YYYYMMDD')
                    AND          edi_type       = 'SN';
                    *\
                    COMMIT;
                 \* EXCEPTION
                    WHEN OTHERS THEN NULL;*\
                  END;   
              END;--#3
           \* WHEN TOO_MANY_ROWS THEN NULL;*\
          END; --#2
        \*WHEN TOO_MANY_ROWS THEN NULL;*\
      END; --#1  */
   /* END IF;*/
  END PR_UPDATE_SN_RDC_HIST_IF01;
  -----------------------------------------------------------------------------------------------------------------  
  -----------------------------------------------------------------------------------------------------------------
  PROCEDURE PR_UPDATE_SN_CDC_HIST_IF
           (p_inv_org                     VARCHAR2
           ,p_model_suffix                VARCHAR2
           ,p_serial_no                   VARCHAR2     
           ,p_shipto_code                 VARCHAR2
           ,p_shipto_date                 VARCHAR2
           ,p_order_no                    VARCHAR2
           ,p_load_plan_no                VARCHAR2)
  -----------------------------------------------------------------------------------------------------------------  
  IS
    v_model                               VARCHAR2(20) := SUBSTR(p_model_suffix,1,INSTR(p_model_suffix,'.')-1);
    v_suffix                              VARCHAR2(20) := SUBSTR(p_model_suffix,INSTR(p_model_suffix,'.')+1
                                                                 ,LENGTH(p_model_suffix) -INSTR(p_model_suffix,'.'));
        
 /*   v_lot_sn                              VARCHAR2(50) := p_box_no;*/
   
  ------------------------------------------------------------------------------------------------------------------                                           
  BEGIN
    INSERT INTO  TB_SN_CDC_HIST
                (inv_org
                ,model_suffix
                ,model
                ,suffix
                ,serial_no
                ,shipto_code
                ,shipto_date
                ,order_no
                ,loadplan_no)
    VALUES      
                (p_inv_org
                ,p_model_suffix
                ,v_model
                ,v_suffix
                ,p_serial_no
                ,p_shipto_code
                ,TO_DATE(p_shipto_date,'YYYYMMDD')
                ,p_order_no
                ,p_load_plan_no);               
    COMMIT;
            
    UPDATE     VNXX_EDI_RESULT
    SET        new_cnt       = TO_NUMBER(new_cnt) +1
    WHERE      edi_date      = TO_CHAR(sysdate,'YYYYMMDD')
    AND        edi_type      = 'SN_CDC';
           
    COMMIT;
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN NULL;
      UPDATE    VNXX_EDI_RESULT
      SET       dup_cnt  = TO_NUMBER(dup_cnt) +1
               ,dup_item = dup_item || p_serial_no || ';'
      WHERE     edi_date = TO_CHAR(sysdate,'YYYYMMDD')
      AND       edi_type = 'SN_CDC';         
                             
      COMMIT;
              
      COMMIT;
  END PR_UPDATE_SN_CDC_HIST_IF;
  -----------------------------------------------------------------------------------------------------------------
  --                                PROCEDURE SERIALS DATA I/F TO GSFS
  -----------------------------------------------------------------------------------------------------------------
  /*PROCEDURE PR_SERIAL_ACTIVE_GSFS_IF
  IS
    v_sqlerrm                              VARCHAR2(4000);
    v_interface_id                         NUMBER;
    v_cnt                                  NUMBER;
  BEGIN
    SELECT COUNT(*)
    INTO   v_cnt
    FROM   
    
  END PR_SERIAL_ACTIVE_GSFS_IF;  */
  PROCEDURE PR_UPDATE_SO_IMEI_GSM 
           (p_imei_no                  VARCHAR2
           ,p_model                    VARCHAR2
           ,p_suffix                   VARCHAR2
           ,p_so_date                  VARCHAR2
           ,p_so_time                  VARCHAR2
           ,p_si_date                  VARCHAR2)
  IS
    v_wt_start_date                    DATE         := TO_DATE(p_so_date,'YYYYMMDD');
    v_wt_end_date                      DATE         := null;
    v_model_div                        VARCHAR2(10) := null; 
    v_so_time                          VARCHAR2(06) := Null;
  BEGIN
    BEGIN
      SELECT                                DISTINCT(prod_l1)
      INTO                                  v_model_div
      FROM                                  TB_CM_MODEL_CAT
      WHERE  model                        = p_model
      GROUP  BY                             prod_l1;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_model_div                      := 'OTHERS';
      WHEN OTHERS THEN
        dbms_output.put_line ('PR_UPDATE_SN_SO_WT_MST :' || SQLERRM || '=>' || p_model);
        write_log;    
    END;
    
    IF v_model_div                       IN (pk_sms_mst.const_wt_2ys, pk_sms_mst.const_wt_2yrs) THEN
       v_wt_end_date                     := v_wt_start_date + (365-1) * 2;
    ELSE
       v_wt_end_date                     := v_wt_start_date + (365-1);   
    END IF;
    
    IF LENGTH(p_so_time) <6 THEN
      v_so_time          := '0'||p_so_time;
    ELSE
      v_so_time          := p_so_time;  
    END IF;  
    BEGIN
      INSERT INTO                        TB_SN_SO_WT_MST0
                                        (model              , suffix
                                        ,serial_no          , wt_start_date
                                        ,wt_end_date        , sellin_date
                                        ,sellout_date       , sellout_time)
      VALUES                            (p_model            , p_suffix
                                        ,p_imei_no          , v_wt_start_date
                                        ,v_wt_end_date      , DECODE(p_si_date, null, null, TO_DATE(p_si_date,'YYYYMMDD'))
                                        ,TO_DATE(p_so_date,'YYYYMMDD'), v_so_time);                                   
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        PR_RAISE_ERR('PR_UPDATE_SN_SO_WT_MST' ,DBMS_UTILITY.format_error_backtrace || '/'|| SQLERRM 
                                              ,p_imei_no || p_model);
        
        ROLLBACK;  
    END;  
           
  END       PR_UPDATE_SO_IMEI_GSM;         
  -----------------------------------------------------------------------------------------------------------------
  --                                PROCEDURE UPDATE DELIVERY & SHIPPING FROM RDC W/Hs
  -----------------------------------------------------------------------------------------------------------------
  PROCEDURE PR_XXVN_DEL_RDC_IF
  IS        --                             PRAGMA AUTONOMOUS_TRANSACTION;
    v_sqlerrm                              VARCHAR2(4000);
  -----------------------------------------------------------------------------------------------------------------
  BEGIN
    BEGIN
      INSERT INTO VNXX_EDI_RESULT
                 (edi_date
                 ,edi_time
                 ,edi_type
                 ,new_cnt
                 ,dup_cnt
                 ,dup_item)
      SELECT      TO_CHAR(sysdate,'YYYYMMDD')
                 ,sysdate
                 ,'DL'
                 ,0
                 ,0
                 ,null
                  FROM dual;
    
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN NULL;  
    END;
  -----------------------------------------------------------------------------------------------------------------
    FOR cur IN(
      SELECT   rowid                     
              ,organization_code         inv_org
              ,delivery_no               delivery_no
              ,source_header_no          order_no
              ,pick_order_no             pick_no
              ,TO_CHAR(transaction_date,'YYYYMMDD')
                                         pick_date
              ,ship_to_customer_code     shipto_code
              ,TO_CHAR(transaction_date,'YYYYMMDD')
                                         ship_date
              ,item_code                 model_suffix
              ,delivery_qty              delivery_qty
              ,TO_CHAR(transfer_date,'YYYYMMDD')
                                         delivery_date
              ,DECODE(status_code, null, 'CLOS', status_code)
                                         delivery_status
              ,pick_seq_no               line_id
      FROM     VNXX_SHPCFM_IF@DBL_MES
      WHERE    transfer_flag           = 'N')
   --   AND      step_status_code        = 'SUCCESS')
      
    LOOP
      BEGIN
        PR_UPDATE_DEL_RDC_HIST_IF(cur.inv_org
                                 ,cur.delivery_no
                                 ,cur.order_no
                                 ,cur.pick_no
                                 ,cur.pick_date
                                 ,cur.shipto_code
                                 ,cur.shipto_code
                                 ,cur.ship_date
                                 ,cur.model_suffix
                                 ,'N/A'
                                 ,cur.delivery_qty
                                 ,cur.delivery_date
                                 ,cur.delivery_status
                                 ,cur.line_id);
                                        
        
        UPDATE VNXX_SHPCFM_IF@DBL_MES
        SET    transfer_flag   = 'Y'
              ,transfer_date   = sysdate 
        WHERE  rowid           = cur.rowid;
        
        COMMIT;                                              
      EXCEPTION
        WHEN OTHERS THEN
          v_sqlerrm                        := SQLERRM;                      
          update VNXX_SHPCFM_IF@DBL_MES
          set    edi_error_message_text     = v_sqlerrm
                ,transfer_date              = sysdate 
          where  rowid                      = cur.rowid;
          ---dbms_output.put_line ('PR_XXVN_DEL_RDC_IF :' || SQLERRM);
          
          NULL;
      END;  
    END LOOP;                                                                                                                                              
  END PR_XXVN_DEL_RDC_IF;                          
  -----------------------------------------------------------------------------------------------------------------
  -----------------------------------------------------------------------------------------------------------------
  PROCEDURE PR_UPDATE_DEL_RDC_HIST_IF
           (p_inv_org                    VARCHAR2
           ,p_delivery_no                VARCHAR2
           ,p_plan_no                    VARCHAR2
           ,p_pick_no                    VARCHAR2
           ,p_pick_date                  VARCHAR2
           ,p_shipto_code                VARCHAR2
           ,p_shipto_name                VARCHAR2
           ,p_ship_date                  VARCHAR2
           ,p_model_suffix               VARCHAR2
           ,p_delivery_address           VARCHAR2
           ,p_delivery_qty               VARCHAR2
           ,p_delivery_date              VARCHAR2
           ,p_delivery_status            VARCHAR2
           ,p_line_id                    VARCHAR2) 
  -----------------------------------------------------------------------------------------------------------------
  IS
    v_model                              VARCHAR2(20) := SUBSTR(p_model_suffix,1,INSTR(p_model_suffix,'.')-1);
    v_suffix                             VARCHAR2(20) := SUBSTR(p_model_suffix,INSTR(p_model_suffix,'.')+1 
                                                                ,LENGTH(p_model_suffix) -INSTR(p_model_suffix,'.'));
  -----------------------------------------------------------------------------------------------------------------
  BEGIN
    INSERT INTO TB_DELIVERY_RDC_HIST
               (inv_org
               ,delivery_no
               ,plan_no
               ,pick_no
               ,pick_date
               ,shipto_code
               ,shipto_name
               ,ship_date
               ,model_suffix
               ,model
               ,suffix
               ,delivery_address
               ,delivery_qty
               ,delivery_date
               ,delivery_status
               ,line_id)
    VALUES
               (p_inv_org
               ,p_delivery_no
               ,p_plan_no
               ,p_pick_no
               ,TO_DATE(p_pick_date,'YYYYMMDD')
               ,p_shipto_code
               ,p_shipto_name
               ,TO_DATE(p_ship_date,'YYYYMMDDHH24MISS')
               ,p_model_suffix
               ,v_model
               ,v_suffix
               ,p_delivery_address
               ,p_delivery_qty
               ,TO_DATE(p_delivery_date,'YYYYMMDDHH24MISS')
               ,p_delivery_status
               ,p_line_id);
       
   UPDATE       VNXX_EDI_RESULT
   SET          new_cnt        = TO_NUMBER(new_cnt) +1
   WHERE        edi_date       = TO_CHAR(sysdate,'YYYYMMDD')
   AND          edi_type       = 'DL';
                  
   COMMIT;                                    
  EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
     UPDATE     VNXX_EDI_RESULT
     SET        dup_cnt      = TO_NUMBER(dup_cnt) +1
               ,dup_item     = dup_item || p_pick_no || p_delivery_no ||';' 
     WHERE      edi_date     = TO_CHAR(sysdate,'YYYYMMDD')
     AND        edi_type     = 'DL';          
               
     COMMIT;
   WHEN OTHERS THEN
     UPDATE     VNXX_EDI_RESULT
     SET        --dup_cnt      = TO_NUMBER(dup_cnt) +1
                dup_item     = dup_item || p_pick_no || p_delivery_no ||';' 
     WHERE      edi_date     = TO_CHAR(sysdate,'YYYYMMDD')
     AND        edi_type     = 'DL';           
  END PR_UPDATE_DEL_RDC_HIST_IF;
  ------------------------------------------------------------------------------------------------------------------
  --                                PROCEDURE UPDATE ORDERs FROM GERP
  ------------------------------------------------------------------------------------------------------------------
  PROCEDURE PR_XXVN_SED_SHIPORDER_IF
  IS      --                                    PRAGMA AUTONOMOUS_TRANSACTION;
   -- cur     SYS_REFCURSOR;
   v_sqlerrm                              VARCHAR2(4000);                                 
  -----------------------------------------------------------------------------------------------------------------
  BEGIN
    BEGIN
      INSERT INTO VNXX_EDI_RESULT
                 (edi_date
                 ,edi_time
                 ,edi_type
                 ,new_cnt
                 ,dup_cnt
                 ,dup_item)
      SELECT      TO_CHAR(sysdate,'YYYYMMDD')
                 ,sysdate
                 ,'SP'
                 ,0
                 ,0
                 ,null
                  FROM dual;
    
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN NULL;  
    END;
    
    FOR cur IN (
      SELECT rowid
            ,source_header_no             ORDER_NO
            ,source_line_no               LINE_NO
            ,pick_order_no                PICK_NO
            ,pick_seq_no                  SEQ_NO
            ,delivery_no                  DELIVERY_NO
            ,delivery_no_gerp             GERP_DEL_NO
            ,organization_code            INV_ORG
            ,item_code                    MODEL_SUFFIX
            ,ordered_quantity             ORDER_QTY
            ,pick_release_qty             RELEASE_QTY
            ,delivery_qty                 DEL_QTY
            ,order_type_name              ORDER_TYPE
            ,TO_CHAR(order_date,'YYYYMMDD')                   
                                          ORDER_DATE
            ,cancel_flag                  CANCEL_FLAG
            ,TO_CHAR(transaction_date,'YYYYMMDD')             
                                          WMS_SHIP_DATE
            ,TO_CHAR(appointment_from_date,'YYYYMMDD')        
                                          APP_DATE
            ,bill_to_customer_code        BILL_TO
            ,bill_to_name                 BILL_NAME
            ,ship_to_customer_code        SHIP_TO
            ,ship_to_short_name           SHIP_NAME
            ,shipping_method_code         SHIP_METHOD
            ,sales_channel_code           SAL_CHANNEL_CODE
            ,sales_channel_name           SAL_CHANNEL_NAME
            ,ship_to_address_line1_info   DEL_ADDRESS
            ,ship_to_city_name            CITY_NAME
            ,carrier_code                 CARRIER
            ,attribute7                   PROD_CAT
            ,DECODE(LENGTH(SUBSTR(REPLACE(REPLACE(attribute14,' 00:00:00',''),'-',''),1,8)),7,TO_CHAR(TO_DATE(SUBSTR(REPLACE(REPLACE(attribute14,' 00:00:00',''),'-',''),1,8),'DDMMYY'),'YYYYMMDD'),
             SUBSTR(REPLACE(REPLACE(attribute14,' 00:00:00',''),'-',''),1,8))                    
                                          DELIVERY_DATE
            ,attribute19                  INVOICE_NO
            ,TO_CHAR(TO_DATE(REPLACE(REPLACE(attribute20,' 00:00:00',''),'-',''),'YYYYMMDD'),'YYYYMMDD')                  
                                          INVOICE_DATE
            ,TO_CHAR(booked_date,'YYYYMMDD')                  
                                          BOOKED_DATE
            ,salesrep_name                SALESPERSON
            ,subinventory                 SUBINVENTORY
            ,ar_flag                      AR_FLAG
            ,au_code                      AU_CODE
            ,acceptance_code              ACCEPTANCE_CODE
      FROM   VNXX_SED_SHPCFM_IF@DBL_MES
      WHERE  transfer_flag NOT IN ('Y'))
    LOOP
      BEGIN
        PR_UPDATE_ORDER_SHIP_HIST(cur.inv_org
                                 ,cur.city_name
                                 ,cur.ship_to
                                 ,cur.ship_name
                                 ,cur.order_no         
                                 ,cur.line_no
                                 ,'PRE-BILLING_ACCEPTANCE'
                                 ,cur.prod_cat
                                 ,cur.model_suffix
                                 ,cur.order_qty
                                 ,cur.release_qty
                                 ,0
                                 ,0
                                 ,cur.ship_method
                                 ,cur.delivery_date
                                 ,cur.wms_ship_date
                                 ,cur.del_address
                                 ,cur.invoice_no
                                 ,cur.invoice_date
                                 ,cur.booked_date
                                 ,cur.pick_no
                                 ,cur.seq_no
                                 ,cur.order_date
                                 ,null
                                 ,cur.subinventory
                                 ,cur.order_date
                                 ,cur.bill_to
                                 ,cur.bill_name
                                 ,cur.sal_channel_code
                                 ,cur.sal_channel_name
                                 ,null
                                 ,cur.salesperson
                                 ,cur.ar_flag
                                 ,cur.au_code
                                 ,cur.acceptance_code
                                 ,cur.carrier
                                 ,cur.order_type
                                 ,cur.cancel_flag);
                                 
        UPDATE VNXX_SED_SHPCFM_IF@DBL_MES
        SET    transfer_flag                = 'Y'
              ,transfer_date                = sysdate 
        WHERE  rowid                        = cur.rowid;
        
        COMMIT;
      EXCEPTION
        WHEN OTHERS THEN
          v_sqlerrm                        := SQLERRM;                      
          update VNXX_SED_SHPCFM_IF@DBL_MES
          set    attribute1                 = v_sqlerrm
                ,transfer_date              = sysdate
                ,transfer_flag              = 'Y' 
          where  rowid                      = cur.rowid;  
      END;
    END LOOP;        
  END PR_XXVN_SED_SHIPORDER_IF;      
  ----------------------------------------------------------------------------------------------------------------  
  PROCEDURE PR_UPDATE_ORDER_SHIP_HIST 
           (p_inv_org                        VARCHAR2 
           ,p_city                           VARCHAR2
           ,p_shipto_code                    VARCHAR2
           ,p_shipto_name                    VARCHAR2
           ,p_order_no                       VARCHAR2
           ,p_line_no                        VARCHAR2
           ,p_line_status                    VARCHAR2
           ,p_prod_div                       VARCHAR2
           ,p_model_suffix                   VARCHAR2
           ,p_order_qty                      VARCHAR2
           ,p_release_qty                    VARCHAR2
           ,p_unit_sell_price                VARCHAR2
           ,p_order_amt                      VARCHAR2
           ,p_shipping_method                VARCHAR2
           ,p_request_shipped_date           VARCHAR2 
           ,p_request_arrival_date           VARCHAR2
           ,p_shipping_remark                VARCHAR2
           ,p_invoice_no                     VARCHAR2
           ,p_invoice_date                   VARCHAR2
           ,p_booked_date                    VARCHAR2
           ,p_pick_no                        VARCHAR2
           ,p_pick_seq                       VARCHAR2
           ,p_pick_release_date              VARCHAR2
           ,p_delivery_no                    VARCHAR2
           ,p_subinventory                   VARCHAR2
           ,p_creation_date                  VARCHAR2
           ,p_billto_code                    VARCHAR2
           ,p_billto_name                    VARCHAR2
           ,p_saleschannel_code              VARCHAR2
           ,p_saleschannel_name              VARCHAR2
           ,p_created_by                     VARCHAR2
           ,p_salesperson                    VARCHAR2
           ,p_ar_flag                        VARCHAR2      DEFAULT null
           ,p_au_code                        VARCHAR2      DEFAULT null
           ,p_acceptance_code                VARCHAR2      DEFAULT null
           ,p_carrier                        VARCHAR2      DEFAULT null
           ,p_order_type_name                VARCHAR2      DEFAULT null
           ,p_cancel_flag                    VARCHAR2      DEFAULT null)
  -----------------------------------------------------------------------------------------------------------------
  IS
    v_model                                  VARCHAR2(20)  := SUBSTR(p_model_suffix,1,INSTR(p_model_suffix,'.')-1);
    v_suffix                                 VARCHAR2(20)  := SUBSTR(p_model_suffix,INSTR(p_model_suffix,'.')+1 
                                                                    ,LENGTH(p_model_suffix) -INSTR(p_model_suffix,'.'));
   -- v_line_status                    VARCHAR2(100) := p_line_status;
    v_line_no                                VARCHAR2(6)   := p_line_no;
    v_prod_div                               VARCHAR2(10)  := REPLACE(p_prod_div,'premium','');
    v_prod_div_dpl                           VARCHAR2(1)   := 'N';
  --------------------------------------------------------------------------------------------------------------
  -----------------------------------------------------------------------------------------------------------------
  BEGIN
  IF v_line_no IS NULL THEN
    v_line_no := 0.0;
  END IF; 
  IF (p_pick_no IS NOT NULL) AND (v_suffix NOT IN ('KMAI')) THEN
    INSERT INTO TB_ORDER_SHIP_HIST
               (inv_org
               ,city
               ,shipto_code
               ,shipto_name
               ,order_no
               ,line_no
               ,line_status
               ,prod_div
               ,model_suffix
               ,model
               ,suffix
               ,order_qty
               ,release_qty
               ,unit_sell_price
               ,order_amt
               ,shipping_method
               ,request_shipped_date
               ,request_arrival_date
               ,shipping_remark
               ,invoice_no
               ,invoice_date
               ,booked_date
               ,pick_no
               ,pick_seq
               ,pick_release_date
               ,delivery_no
               ,subinventory
               ,creation_date
               ,billto_code
               ,billto_name
               ,saleschannel_code
               ,saleschannel_name
               ,created_by
               ,salesperson
               ,ar_flag
               ,au_code
               ,acceptance_code
               ,carrier
               ,order_type_name
               ,cancel_flag)
     VALUES 
               (p_inv_org
               ,p_city
               ,p_shipto_code
               ,p_shipto_name
               ,p_order_no
               ,v_line_no
               ,p_line_status
               ,v_prod_div
               ,p_model_suffix
               ,v_model
               ,v_suffix
               ,p_order_qty
               ,p_release_qty
               ,p_unit_sell_price
               ,p_order_amt
               ,p_shipping_method
               ,DECODE(p_request_shipped_date, null, null, TO_DATE(p_request_shipped_date,'YYYYMMDD'))
               ,DECODE(p_request_arrival_date, null, null, TO_DATE(p_request_arrival_date,'YYYYMMDD'))
               ,p_shipping_remark
               ,p_invoice_no
               ,DECODE(p_invoice_date, null, null, TO_DATE(p_invoice_date,'YYYYMMDD'))
               ,DECODE(p_booked_date, null, null, TO_DATE(p_booked_date,'YYYYMMDD'))
               ,p_pick_no
               ,p_pick_seq
               ,DECODE(p_pick_release_date, null, null, TO_DATE(p_pick_release_date,'YYYYMMDD'))
               ,p_delivery_no
               ,p_subinventory
               ,TO_DATE(p_creation_date,'YYYYMMDD')
               ,p_billto_code
               ,p_billto_name
               ,p_saleschannel_code
               ,p_saleschannel_name
               ,p_created_by
               ,p_salesperson
               ,p_ar_flag
               ,p_au_code
               ,p_acceptance_code
               ,p_carrier
               ,p_order_type_name
               ,p_cancel_flag);
      
      COMMIT;
          
      UPDATE    VNXX_EDI_RESULT
      SET       new_cnt    = TO_NUMBER(new_cnt) +1
      WHERE     edi_date   = TO_CHAR(sysdate,'YYYYMMDD')
      AND       edi_type   = 'SP';  
        
      COMMIT;
         
    END IF;
    
    --/Insert model info to tb_cm_model_cat
    BEGIN 
      SELECT 'Y'
      INTO   v_prod_div_dpl
      FROM   TB_CM_MODEL_CAT
      WHERE  LTRIM(RTRIM(model)) = LTRIM(RTRIM(v_model));
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        INSERT INTO TB_CM_MODEL_CAT
                   (prod_l1
                   ,model
                   ,suffix)
        VALUES     
                   (p_prod_div
                   ,v_model
                   ,v_suffix);

      WHEN TOO_MANY_ROWS THEN NULL;                                                                        
    END;   
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN NULL;
      UPDATE     VNXX_EDI_RESULT
      SET        dup_cnt       = TO_NUMBER(dup_cnt) +1
                ,dup_item     = dup_item || p_order_qty || p_pick_no || p_model_suffix || ';' 
      WHERE      edi_date   = TO_CHAR(sysdate,'YYYYMMDD')
      AND        edi_type   = 'SP';  
      
      COMMIT;                                                                       
  END PR_UPDATE_ORDER_SHIP_HIST;                                       
  -----------------------------------------------------------------------------------------------------------------
  --                                PROCEDURE UPDATE MODEL CATALOGY
  -----------------------------------------------------------------------------------------------------------------
  PROCEDURE PR_XXVN_MODEL_IF
  IS
    v_sqlerrm                              VARCHAR2(4000);          
  BEGIN
    BEGIN
      INSERT INTO VNXX_EDI_RESULT
                 (edi_date
                 ,edi_time
                 ,edi_type
                 ,new_cnt
                 ,dup_cnt
                 ,dup_item)
      SELECT      TO_CHAR(sysdate,'YYYYMMDD')
                 ,sysdate
                 ,'MD'
                 ,0
                 ,0
                 ,null
                  FROM dual;
    
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN NULL;  
    END;
  -----------------------------------------------------------------------------------------------------------------
    FOR cur IN(
      SELECT   rowid                     
              ,product_level1_code       prod_l1
              ,sales_model_code          model
              ,TO_CHAR(creation_date,'YYYYMMDD')     
                                         create_date
              ,sales_suffix_code         suffix
              ,model_code                mp_model
              ,suffix_code               mp_suffix
              ,disabled_status_code      active_code
              ,sales_model_flag          sales_flag
              ,product_level2_code       prod_l2
              ,product_level3_code       prod_l3
              ,product_level4_code       prod_l4
              ,tentative_model_flag      tentative_flag
              ,model_desc                model_spec
              ,purchase_type_code        pur_type
              ,enabled_flag              enable_flag
              ,TO_CHAR(last_update_date,'YYYYMMDD')
                                         last_update_date
              ,user_item_type_code       unit                               
              ,hq_accounting_unit_code   au_code
              ,affiliate_code            af_code
              ,primary_uom_code          eom
              ,product_goods_type_code   prod_type
              ,market_code               mkt
      FROM     lge.XVH_MODEL_MASTER_IF@VHDEV --VNXX_MODEL_MASTER_IF@DBL_MES
      WHERE    transfer_flag           = 'N')
      
    LOOP
      BEGIN
        PR_UPDATE_MODEL_CAT(cur.prod_l1               
                           ,cur.model                 
                           ,cur.create_date           
                           ,cur.suffix                
                           ,cur.mp_model              
                           ,cur.mp_suffix             
                           ,cur.active_code           
                           ,cur.sales_flag            
                           ,cur.prod_l2               
                           ,cur.prod_l3               
                           ,cur.prod_l4               
                           ,cur.tentative_flag        
                           ,cur.model_spec            
                           ,cur.pur_type              
                           ,cur.enable_flag           
                           ,cur.last_update_date      
                           ,cur.unit                  
                           ,cur.au_code               
                           ,cur.af_code               
                           ,cur.eom                   
                           ,cur.prod_type             
                           ,cur.mkt);
                                        
        
        UPDATE lge.XVH_MODEL_MASTER_IF@VHDEV -- VNXX_MODEL_MASTER_IF@DBL_MES
        SET    transfer_flag   = 'Y'
              ,transfer_date   = sysdate 
        WHERE  rowid           = cur.rowid;
        
        COMMIT;                                              
      EXCEPTION
        WHEN OTHERS THEN
          v_sqlerrm                        := SQLERRM;                      
          update lge.XVH_MODEL_MASTER_IF@VHDEV --VNXX_MODEL_MASTER_IF@DBL_MES
          set    attribute50                = v_sqlerrm
                ,transfer_date              = sysdate 
          where  rowid                      = cur.rowid;
          
          NULL;
      END;  
    END LOOP;
  END PR_XXVN_MODEL_IF;
  -----------------------------------------------------------------------------------------------------------------
  -----------------------------------------------------------------------------------------------------------------
  PROCEDURE PR_UPDATE_MODEL_CAT 
           (p_prod_l1               VARCHAR2
           ,p_model                 VARCHAR2
           ,p_create_date           VARCHAR2
           ,p_suffix                VARCHAR2
           ,p_mp_model              VARCHAR2
           ,p_mp_suffix             VARCHAR2
           ,p_active_code           VARCHAR2
           ,p_sales_flag            VARCHAR2
           ,p_prod_l2               VARCHAR2
           ,p_prod_l3               VARCHAR2
           ,p_prod_l4               VARCHAR2
           ,p_tentative_flag        VARCHAR2
           ,p_model_spec            VARCHAR2
           ,p_pur_type              VARCHAR2
           ,p_enable_flag           VARCHAR2
           ,p_last_update_date      VARCHAR2
           ,p_unit                  VARCHAR2
           ,p_au_code               VARCHAR2
           ,p_af_code               VARCHAR2
           ,p_eom                   VARCHAR2
           ,p_prod_type             VARCHAR2
           ,p_mkt                   VARCHAR2 )
  -----------------------------------------------------------------------------------------------------------------
  IS
   v_model                          VARCHAR2(30) := p_model;
   v_temp                           VARCHAR2(1)  := null;
  -----------------------------------------------------------------------------------------------------------------
  BEGIN
    SELECT 'Y'
    INTO   v_temp
    FROM   tb_cm_model_cat
    WHERE  model = v_model;
  EXCEPTION
   WHEN NO_DATA_FOUND THEN
     INSERT INTO TB_CM_MODEL_CAT
                 (SELECT      
                        p_prod_l1
                       ,p_model 
                       ,TO_DATE(p_create_date,'YYYYMMDDHH24MISS') 
                       ,p_suffix 
                       ,p_mp_model 
                       ,p_mp_suffix 
                       ,p_active_code 
                       ,p_sales_flag 
                       ,p_prod_l2 
                       ,p_prod_l3 
                       ,p_prod_l4 
                       ,p_tentative_flag
                       ,p_model_spec 
                       ,p_pur_type 
                       ,p_enable_flag 
                       ,TO_DATE(p_last_update_date,'YYYYMMDDHH24MISS') 
                       ,p_unit 
                       ,p_au_code 
                       ,p_af_code 
                       ,p_eom 
                       ,p_prod_type 
                       ,p_mkt
                       ,sysdate
                       ,'N'
                       ,sysdate  
                       FROM dual);
       
     UPDATE       VNXX_EDI_RESULT
     SET          new_cnt        = TO_NUMBER(new_cnt) +1
     WHERE        edi_date       = TO_CHAR(sysdate,'YYYYMMDD')
     AND          edi_type       = 'MD';
                     
     COMMIT;              
   WHEN DUP_VAL_ON_INDEX THEN null;
     UPDATE     VNXX_EDI_RESULT
     SET        dup_cnt      = TO_NUMBER(dup_cnt) +1
               ,dup_item     = dup_item || p_prod_l1 || p_model ||p_suffix||';'
     WHERE      edi_date     = TO_CHAR(sysdate,'YYYYMMDD')
     AND        edi_type     = 'MD';   
       
     COMMIT;        
  END PR_UPDATE_MODEL_CAT;
  -----------------------------------------------------------------------------------------------------------------
  --
  -----------------------------------------------------------------------------------------------------------------
  PROCEDURE PR_UPDATE_CUST_EDI
           (p_custcode            VARCHAR2
           ,p_custkey             VARCHAR2
           ,p_serial_no           VARCHAR2
           ,p_model               VARCHAR2
           ,p_sell_date           VARCHAR2
           ,p_eucell              VARCHAR2
           ,p_euname              VARCHAR2
           ,p_euadd               VARCHAR2
           ,p_resptime            VARCHAR2
           ,p_edi_file            VARCHAR2)
  IS
            v_sellout_date        DATE        :=TO_DATE(p_sell_date,'YYYYMMDD');
            v_resptime            DATE        :=TO_DATE(p_resptime,'YYYYMMDDHH24MISS');
            v_edi_date            VARCHAR2(08):=SUBSTR(p_resptime,1,8);
            v_head                VARCHAR2(10):=SUBSTR(p_edi_file,1,INSTR(p_edi_file,'_')-1);
            
            v_resp_type           VARCHAR2(10);
            v_resp_msg            VARCHAR2(160);
            v_cmdcode             VARCHAR2(10);
            
            v_eucell              VARCHAR2(20);
            v_serial_no           VARCHAR2(50);
            v_sal_model           VARCHAR2(50);
            v_suffix              VARCHAR2(50);
            v_sellin_date         DATE;
            v_dup_chk             SN_DUP_TYPE  := SN_DUP_TYPE (null, null, null);
            v_dup                 VARCHAR2(01);
            v_wt_resp             WT_RESP_TYPE := WT_RESP_TYPE(null, null, null);
  -----------------------------------------------------------------------------------------------------------------
  BEGIN
    BEGIN
      SELECT 'OK'
      INTO   v_dup
      FROM   TB_EDI_RESULT
      WHERE  edi_date             = TO_CHAR(sysdate,'YYYYMMDD')
      AND    edi_file             = edi_file
      AND    edi_head             = v_head;      
    EXCEPTION
      WHEN NO_DATA_FOUND THEN  
        INSERT INTO TB_EDI_RESULT( 
                    edi_date
                   ,edi_file
                   ,edi_code
                   ,edi_head)
        VALUES     (TO_CHAR(sysdate,'YYYYMMDD')
                   ,p_edi_file
                   ,'MC-EDI'
                   ,v_head);                            
    
        COMMIT;
      WHEN OTHERS THEN NULL;  
    END;
    
    IF p_eucell  IS NOT NULL THEN
      SELECT        DECODE(SUBSTR(p_eucell,1,2), '84', p_eucell
                                 ,'84'|| DECODE( SUBSTR(p_eucell,1,1)
                                 ,'0'
                                 ,SUBSTR(p_eucell, 2, LENGTH(p_eucell)-1)
                                 ,p_eucell))
      INTO                        v_eucell             
      FROM                        DUAL;
    END IF; 
    IF p_serial_no IS NULL THEN
      v_resp_type  := pk_sms_mst.const_sms_nvl;
      v_resp_msg   := pk_sms_mst.FN_SMS_TRANS(pk_sms_mst.FN_GET_MT_RESPONSE(v_resp_type, '$'||'_'));
      v_cmdcode    := pk_sms_mst.const_cmdcode_so;
      
      --Update SO history
      pk_sms_mst.PR_UPDATE_SN_SO_WT_HIST( p_serial_no     , v_resp_type 
                                        , v_resp_msg      , 'MC-SO'      
                                        , v_resptime      , null             
                                        , v_eucell        , 'N'              
                                        , p_custkey       , 'EDI'            
                                        , p_model         , p_custcode     
                                        , 1               , p_edi_file);
      INSERT INTO TB_SN_SO_DEALERS
                  (shop_code
                  ,box_no
                  ,model
                  ,model_desc
                  ,edi_no
                  ,sellout_date
                  ,eu_cell
                  ,eu_name
                  ,eu_address
                  ,update_date
                  ,edi_date
                  ,edi_file
                  ,vrf_flag)
      VALUES
                  (p_custcode
                  ,p_serial_no
                  ,p_model
                  ,p_model
                  ,p_custkey
                  ,v_sellout_date
                  ,p_eucell
                  ,p_euname
                  ,p_euadd
                  ,v_resptime
                  ,v_edi_date
                  ,p_edi_file
                  ,'N');
       
      COMMIT;
      
      UPDATE  TB_EDI_RESULT
      SET     fail_cnt                 = fail_cnt +1
             ,fail_item                = fail_item || '/' || p_custkey
      WHERE   edi_date                 = TO_CHAR(sysdate,'YYYYMMDD')
      AND     edi_file                 = p_edi_file;
    
      COMMIT;
      
      RETURN;                                  
    END IF;
      
    IF LENGTH(p_serial_no) > 13 THEN
      v_serial_no := FN_EDI_BOX_CHK(p_serial_no);
    ELSE
      v_serial_no := p_serial_no;
    END IF;
    
    v_dup_chk     := pk_sms_mst.FN_SN_DUP_CHECK(v_serial_no, p_model, pk_sms_mst.const_so_sn_inc_dup,pk_sms_mst.const_cmdcode_so);
    IF v_dup_chk.DUP_FLAG = 'D' THEN
      v_resp_type := pk_sms_mst.const_so_del_dup;
      v_resp_msg  := pk_sms_mst.FN_SMS_TRANS(pk_sms_mst.FN_GET_MT_RESPONSE(v_resp_type , v_dup_chk.RESP_MSG));
      
      --Update SO history
      pk_sms_mst.PR_UPDATE_SN_SO_WT_HIST(v_serial_no       , v_resp_type 
                                        ,v_resp_msg        , 'MC-SO'      
                                        ,v_resptime        ,  null         
                                        ,v_eucell          , 'N'          
                                        ,p_custkey         , 'EDI'                 
                                        ,p_model           , p_custcode                  
                                        ,3                 , p_edi_file);
      INSERT INTO TB_SN_SO_DEALERS
                 (shop_code
                 ,box_no
                 ,serial_no
                 ,model
                 ,model_desc
                 ,edi_no
                 ,sellout_date
                 ,eu_cell
                 ,eu_name
                 ,eu_address
                 ,update_date
                 ,edi_date
                 ,edi_file
                 ,vrf_flag)
      VALUES
                 (p_custcode
                 ,p_serial_no
                 ,v_serial_no
                 ,p_model
                 ,p_model
                 ,p_custkey
                 ,v_sellout_date
                 ,v_eucell
                 ,p_euname
                 ,p_euadd
                 ,v_resptime
                 ,v_edi_date
                 ,p_edi_file
                 ,'Y');
         
      COMMIT;
      
      UPDATE  TB_EDI_RESULT
      SET     dup_cnt                  = dup_cnt +1
             ,dup_item                 = dup_item || '/' || p_custkey
      WHERE   edi_date                 = TO_CHAR(sysdate,'YYYYMMDD')
      AND     edi_file                 = p_edi_file;
    
      COMMIT;
      
    ELSE 
      BEGIN  
        /*IF p_model     IS NULL THEN    
          SELECT       serial_no
                      ,model
                      ,out_date
          INTO         v_serial_no
                      ,v_sal_model
                      ,v_sellin_date
          FROM         VW_SN_NM
          WHERE        serial_no     = v_serial_no;
        ELSE*/
        SELECT       serial_no
                    ,model
                    ,suffix
                    ,out_date
        INTO         v_serial_no
                    ,v_sal_model
                    ,v_suffix
                    ,v_sellin_date
        FROM         VW_SN_NM
        WHERE        serial_no     = v_serial_no
        AND          TRIM(model)   = p_model
        AND          out_date      =(SELECT MAX(out_date)
                                     FROM   VW_SN_NM
                                     WHERE  serial_no            = v_serial_no
                                     AND    TRIM(model)          = p_model);
       -- END IF;
        
        v_resp_type     := pk_sms_mst.const_so_sn_vl;
        v_resp_msg      := pk_sms_mst.FN_SMS_TRANS(pk_sms_mst.FN_GET_MT_RESPONSE( v_resp_type ,'$' || v_sal_model || ', Serial ' || v_serial_no));
        pk_sms_mst.PR_UPDATE_SN_SO_WT_MST (p_custcode        , null
                                          ,v_serial_no       , v_sal_model
                                          ,v_suffix          , v_sellin_date
                                          ,v_sellout_date    , null
                                          ,v_resp_type       , v_resp_msg
                                          ,'N'               , v_eucell      
                                          ,'Y'               , 'Y');
          
        --Update SO history
        pk_sms_mst.PR_UPDATE_SN_SO_WT_HIST(v_serial_no       , v_resp_type 
                                          ,v_resp_msg        , 'MC-SO'      
                                          ,v_resptime        ,  null         
                                          ,v_eucell          , 'N'          
                                          ,p_custkey         , 'EDI'                 
                                          ,v_sal_model       , p_custcode                  
                                          ,0                 , p_edi_file);
                                               
        UPDATE TB_SN_RDC_HIST
        SET    sellout_status     = 'Y'
              ,sellout_date       = v_sellout_date
        WHERE  serial_no          = v_serial_no
        AND    model              = v_sal_model;
                        
        UPDATE TB_SN_CDC_HIST
        SET    sellout_flag       = 'Y'
              ,sellout_date       = v_sellout_date
        WHERE  serial_no          = v_serial_no
        AND    model              = v_sal_model;
        
        v_wt_resp     := pk_sms_mst.FN_GET_WT_RESPONSE(v_serial_no , v_sal_model);              
        
        IF v_wt_resp.wt_resp_flag = 'O' THEN
          IF v_wt_resp.wt_resp_year = 1 THEN
            v_resp_type   := pk_sms_mst.const_wt_12_vl;
          ELSIF v_wt_resp.wt_resp_year = 2 THEN
            v_resp_type   := pk_sms_mst.const_wt_24_vl;
          END IF;  
        END IF;  
        
        v_resp_msg    := pk_sms_mst.FN_GET_MT_RESPONSE(v_resp_type ,v_wt_resp.wt_resp_msg);
          
        INSERT INTO      TB_MT_HIST(
                          moseq                        ,cell_no
                         ,cmdcode                      ,resp_type
                         ,msgbody                      ,mo_msgbody
                         ,mo_receive_time)
        VALUES                      (      
                          '0'                          , v_eucell
                         ,pk_sms_mst.const_cmdcode_wt  , v_resp_type
                         ,v_resp_msg                   , 'MC-EDI'
                         ,v_resptime);
        COMMIT;      
        --Update WT history
        pk_sms_mst.PR_UPDATE_SN_SO_WT_HIST (v_serial_no      , v_resp_type 
                                           ,v_resp_msg       , 'MC-EDI'
                                           ,v_resptime       , null                
                                           ,v_eucell         , 'Y'                 
                                           ,p_custkey        , 'LGBH'               
                                           ,v_sal_model      , p_custcode      
                                           ,'Y'              , p_edi_file);                                                                                          
        INSERT INTO TB_SN_SO_DEALERS
                   (shop_code
                   ,box_no
                   ,serial_no
                   ,model
                   ,model_desc
                   ,edi_no
                   ,sellout_date
                   ,eu_cell
                   ,eu_name
                   ,eu_address
                   ,update_date
                   ,edi_date
                   ,edi_file
                   ,vrf_flag)
        VALUES
                   (p_custcode
                   ,p_serial_no
                   ,v_serial_no
                   ,v_sal_model
                   ,v_sal_model
                   ,p_custkey
                   ,v_sellout_date
                   ,v_eucell
                   ,p_euname
                   ,p_euadd
                   ,v_resptime
                   ,v_edi_date
                   ,p_edi_file
                   ,'Y');
           
        COMMIT;
                             
        UPDATE   TB_EDI_RESULT
        SET      done_cnt      = done_cnt +1
                ,done_item     = done_item || '/' || p_custkey
        WHERE    edi_date      = TO_CHAR(sysdate,'YYYYMMDD')
        AND      edi_file      = p_edi_file;
        
        COMMIT;  
      
      EXCEPTION
        WHEN NO_DATA_FOUND THEN      
          v_resp_type     := pk_sms_mst.const_sn_pnd_vl;
          v_resp_msg      := pk_sms_mst.FN_SMS_TRANS(pk_sms_mst.FN_GET_MT_RESPONSE( v_resp_type ,'$' || v_serial_no));
          
          --Update SO history
          pk_sms_mst.PR_UPDATE_SN_SO_WT_HIST(v_serial_no       , v_resp_type 
                                            ,v_resp_msg        , 'MC-SO'      
                                            ,v_resptime        ,  null         
                                            ,v_eucell          , 'N'          
                                            ,p_custkey         , 'EDI'                 
                                            ,v_sal_model       , p_custcode                  
                                            ,2                 , p_edi_file);    
          --Update Pending serial
          pk_sms_mst.PR_UPDATE_PND_HIST     (v_serial_no       , v_sal_model 
                                            ,'EDI'             , p_custkey                 
                                            ,p_custcode        , null  
                                            ,v_eucell          , v_resp_type 
                                            ,v_resp_type       , v_resp_msg     
                                            ,'MC-SO'           , v_resptime);
                                          
          INSERT INTO TB_SN_SO_DEALERS
                    (shop_code
                    ,box_no
                    ,serial_no
                    ,model
                    ,model_desc
                    ,edi_no
                    ,sellout_date
                    ,eu_cell
                    ,eu_name
                    ,eu_address
                    ,update_date
                    ,edi_date
                    ,edi_file
                    ,vrf_flag)
          VALUES
                    (p_custcode
                    ,p_serial_no
                    ,v_serial_no
                    ,p_model
                    ,p_model
                    ,p_custkey
                    ,v_sellout_date
                    ,v_eucell
                    ,p_euname
                    ,p_euadd
                    ,v_resptime
                    ,v_edi_date
                    ,p_edi_file
                    ,'N');
           
          COMMIT;
          
          UPDATE  TB_EDI_RESULT
          SET     fail_cnt                 = fail_cnt +1
                 ,fail_item                = fail_item || '/' || p_custkey
          WHERE   edi_date                 = TO_CHAR(sysdate,'YYYYMMDD')
          AND     edi_file                 = p_edi_file;
          
          COMMIT;
       -- WHEN OTHERS THEN NULL;
      END;
    END IF; 
  END  PR_UPDATE_CUST_EDI;
  -----------------------------------------------------------------------------------------------------------------
  --                                FUNCTION RETURN UPDATEs RESULTs
  -----------------------------------------------------------------------------------------------------------------
  FUNCTION FN_EDI_BOX_CHK
          (p_box_no           VARCHAR2)
  RETURN                      VARCHAR2 
  IS
    v_serial_no               VARCHAR2(50);                          
  BEGIN
    SELECT                    distinct(SERIAL_NO)
    INTO                      v_serial_no      
    FROM (
          SELECT              customer_model_serial_no SERIAL_NO     
          FROM                XXOWMM_MES_SN2_S_IF@DBL_MES
          WHERE               item_serial_no         = p_box_no
    UNION
          SELECT              serial_no                SERIAL_NO
          FROM                LKALSMGR.TBL_SERIAL_NO_PRINTED_SERIAL@DBL_MES
          WHERE               serial_no LIKE (SELECT '%'||serial
                                              FROM     LKALSMGR.TBL_SERIAL_NO_PRINTED_SERIAL@DBL_MES
                                              WHERE    serial_no = p_box_no) 
          AND                 work_order   = (SELECT work_order
                                              FROM     LKALSMGR.TBL_SERIAL_NO_PRINTED_SERIAL@DBL_MES
                                              WHERE    serial_no = p_box_no)
          AND                 serial_no_usage LIKE     'DISPLAY_SET_BRAND%');
  
    RETURN                    v_serial_no;  
  EXCEPTION
    WHEN OTHERS THEN RETURN   p_box_no;   
  END      FN_EDI_BOX_CHK;
  -----------------------------------------------------------------------------------------------------------------                          
  FUNCTION FN_EDI_RLT
          (p_filename         VARCHAR2
          ,p_filesize         VARCHAR2
          ,p_item             VARCHAR2)
  RETURN                      VARCHAR2
  -----------------------------------------------------------------------------------------------------------------
  IS
    v_return                  VARCHAR2(200) := null; 
  -----------------------------------------------------------------------------------------------------------------
  BEGIN
    SELECT   'Result [Success : '  || success_cnt 
           ||', Duplicated : '     || dup_cnt     || ' ]'
    INTO     v_return
    FROM     TB_EDI_HIST
    WHERE    edi_filename = p_filename
    AND      edi_filesize = p_filesize
    AND      item_id      = p_item;
    
    RETURN v_return;             
  END FN_EDI_RLT;
  -----------------------------------------------------------------------------------------------------------------
  PROCEDURE PR_SO_WT_MST_SW_EDI
           (out_cur          OUT   SYS_REFCURSOR)
  IS
  -----------------------------------------------------------------------------------------------------------------
  BEGIN
    OPEN out_cur FOR
      SELECT *
      FROM   tb_sn_so_wt_mst
      WHERE  so_transfer_flag='N';
  END       PR_SO_WT_MST_SW_EDI;
  -----------------------------------------------------------------------------------------------------------------
  PROCEDURE PR_SN_RDC_SW_EDI
           (out_cur          OUT   SYS_REFCURSOR)
  IS
  -----------------------------------------------------------------------------------------------------------------
  BEGIN
    OPEN out_cur FOR
      SELECT *
      FROM   tb_sn_rdc_hist
      WHERE  so_transfer_flag='N';
  END       PR_SN_RDC_SW_EDI;
  -----------------------------------------------------------------------------------------------------------------
  PROCEDURE PR_SN_CDC_SW_EDI
           (out_cur          OUT   SYS_REFCURSOR)
  IS
  -----------------------------------------------------------------------------------------------------------------
  BEGIN
    OPEN out_cur FOR
      SELECT *
      FROM   tb_sn_cdc_hist
      WHERE  so_transfer_flag='N';
  END       PR_SN_CDC_SW_EDI; 
  ----------------------------------------------------------------------------------------------
  PROCEDURE PR_MT_ADD_SEND
  IS
    v_mt_cnt    NUMBER;
  BEGIN
    SELECT count(mt_seq)
    INTO   v_mt_cnt
    FROM   tb_mt_send;
    
    IF v_mt_cnt = 0 THEN
      INSERT INTO tb_mt_send
                 (moseq
                 ,shortcode
                 ,cell_no
                 ,msgbody
                 ,msgtype
                 ,mttotalseq
                 ,mtseqref
                 ,cpid
                 ,mt_seq)
      SELECT      moseq
                 ,shortcode
                 ,cell_no
                 ,msgbody
                 ,msgtype
                 ,mttotalseq
                 ,mtseqref
                 ,cpid
                 ,mt_seq
      FROM        tb_mt_hist
      WHERE       send_flag='N'; 
      --COMMIT;
      
      UPDATE      tb_mt_hist
      SET         send_flag ='Y'
                 ,result    =0
                /* ,send_time =TO_CHAR(sysdate,'MMDDYYYYHH24MISS')
                 ,finish_time =TO_CHAR(sysdate,'MMDDYYYYHH24MISS')  */
      WHERE       send_flag ='N';
      
      COMMIT;                    
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      DELETE tb_mt_hist
      WHERE  send_flag='N'
      AND mt_seq is null;
      COMMIT;                
  END       PR_MT_ADD_SEND;                                      
END pk_edi_mst;
